﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonLibrary.DataModel.Plan;
using System.Data.SqlClient;
using CommonLibrary.DB;
using System.Data;

namespace ServiceApplication.Dao.Plan
{
    public class GroupDao
    {
        public int AddEntity(GroupEntity groupEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@group_name", groupEntity.group_name));
            parameterList.Add(new SqlParameter("@group_users", groupEntity.group_users));
            parameterList.Add(new SqlParameter("@creater_id", groupEntity.creater_id));
            parameterList.Add(new SqlParameter("@group_createtime", groupEntity.group_createtime));
            parameterList.Add(new SqlParameter("@group_descn", groupEntity.group_descn));


            string sql = "insert into plan_group (group_name,group_users,creater_id,group_createtime,group_descn) values " +
                " (@group_name,@group_users,@creater_id,@group_createtime,@group_descn); " +
                " select @@identity";
            string id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(id);
        }

        public GroupEntity QueryEntity(int group_id)
        {
            GroupEntity groupEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@group_id", group_id));
            string sql = "select group_id,group_name,group_users,creater_id,group_createtime,group_descn  from plan_group where group_id=@group_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (null==result) return null;

            groupEntity = new GroupEntity();
            groupEntity.group_id = int.Parse(result[0][0]);
            groupEntity.group_name = result[0][1];
            groupEntity.group_users = result[0][2];
            groupEntity.creater_id = int.Parse(result[0][3]);
            groupEntity.group_createtime = result[0][4];
            groupEntity.group_descn = result[0][5];
            return groupEntity;
        }

        public GroupEntity QueryEntity(int creater_id,string group_name)
        {
            GroupEntity groupEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@creater_id", creater_id));
            parameterList.Add(new SqlParameter("@group_name", group_name));
            string sql = "select group_id,group_name,group_users,creater_id,group_createtime,group_descn "+
                             " from plan_group where creater_id=@creater_id and group_name=@group_name ";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result ==null) return null;

            groupEntity = new GroupEntity();
            groupEntity.group_id = int.Parse(result[0][0]);
            groupEntity.group_name = result[0][1];
            groupEntity.group_users = result[0][2];
            groupEntity.creater_id = int.Parse(result[0][3]);
            groupEntity.group_createtime = result[0][4];
            groupEntity.group_descn = result[0][5];
            return groupEntity;
        }

        public void DeleteEntity(int group_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@group_id", group_id));
            string sql = "delete from plan_group where group_id=@group_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void UpadateEntity(GroupEntity groupEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@group_id", groupEntity.group_id));
            parameterList.Add(new SqlParameter("@group_name", groupEntity.group_name));
            parameterList.Add(new SqlParameter("@group_users", groupEntity.group_users));
            parameterList.Add(new SqlParameter("@creater_id", groupEntity.creater_id));
            parameterList.Add(new SqlParameter("@group_createtime", groupEntity.group_createtime));
            parameterList.Add(new SqlParameter("@group_descn", groupEntity.group_descn));

            string sql = "update plan_group set group_name=@group_name,group_users=@group_users,creater_id=@creater_id, " +
                             " group_createtime=@group_createtime,group_descn=@group_descn where group_id=@group_id ";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public DataTable QueryByCreaterId(int creater_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@creater_id", creater_id));
            string sql = "select group_id 编码,group_name 名称,group_users 成员,creater_id 创建人编码,group_createtime 创建时间,group_descn 描述  from plan_group where creater_id=@creater_id";

            return DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());

        }
    }
}
